package csm.sql;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.io.InvalidClassException;
import java.io.Reader;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.XMLOutputter;

import csm.common.ListHelper;
import csm.common.SMException;
import csm.common.StringUtil;
import csm.common.xml.XmlDocument;
import csm.sql.annotation.ColumnMapping;
import csm.sql.annotation.TableMapping;

/**
 * Database의 Table을 모델링하는 Entity 클래스의 추상클래스 입니다.<br>
 * 이 클래스를 통해, 테이블에 대한 간단한 CRUD 도구를 제공합니다.
 * @author seungmun
 * @since 2006.1.18.
 * @version
 * @see
 */
@SuppressWarnings("unchecked")
public abstract class ObjectMappedEntityBase extends EntityBase
{
	@SuppressWarnings("rawtypes")
	private static final Map primitiveDefaults = new HashMap();

	static
	{
		primitiveDefaults.put(Integer.TYPE, new Integer(0));
		primitiveDefaults.put(Short.TYPE, new Short((short) 0));
		primitiveDefaults.put(Byte.TYPE, new Byte((byte) 0));
		primitiveDefaults.put(Float.TYPE, new Float(0));
		primitiveDefaults.put(Double.TYPE, new Double(0));
		primitiveDefaults.put(Long.TYPE, new Long(0));
		primitiveDefaults.put(Boolean.TYPE, Boolean.FALSE);
		primitiveDefaults.put(Character.TYPE, new Character('\u0000'));
	}

	private class QueryBuilder
	{
		ArrayList<ParameterSet> paramSet = null;
		DBConfig dbc = null;

		public ArrayList<ParameterSet> getParameterSet()
		{
			return this.paramSet;
		}

		public QueryBuilder(DBConfig dbc)
		{
			this.paramSet = new ArrayList<ParameterSet>();
			this.dbc = dbc;
		}

		/**
		 * 매핑클래스인지 여부를 체크하는 메서드
		 * 
		 * @param mappedObject
		 *            체크할 객체
		 * @param map
		 *            매핑정보
		 * @throws InvalidClassException
		 *             매핑정보가 없을때, 매핑정보
		 */
		private <T extends ObjectMappedEntityBase> void checkMapping(T mappedObject, TableMapping map) throws InvalidClassException
		{
			if (map == null)
			{
				throw new InvalidClassException("Table Mapping클래스가 아닙니다. . Class : " + mappedObject.getClass().getName());
			}
			if (map.tableName() == null)
			{
				throw new InvalidClassException("Table Mapping정보가 잘못되었습니다. Class : " + mappedObject.getClass().getName());
			}
		}

		/**
		 * 
		 * @param mappedObject
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createExistQuery(T mappedObject) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			String query = StringUtil.replaceAll("SELECT 1 FROM dual WHERE exists (%READ%)", "%READ%", createReadQuery(mappedObject));
			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @param columns
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createCountQuery(T mappedObject, String[] columns) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);

			checkMapping(mappedObject, map);

			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COUNT(*) AS rowcnt FROM ");
			sb.append(tableName);
			sb.append(" %WHERE% ");

			StringBuffer sbClause = new StringBuffer();
			if (columns != null && columns.length > 0)
			{
				for (Method info : mappedObject.getClass().getMethods())
				{
					ColumnMapping att = info.getAnnotation(ColumnMapping.class);
					if (att == null) continue;
					boolean isFind = false;
					for (String column : columns)
					{
						if (att.columnName().equalsIgnoreCase(column))
						{
							isFind = true;
							break;
						}
					}
					if (!isFind) continue;
					if (!info.getName().startsWith("get")) continue;
					sbClause.append("AND " + att.columnName() + " = ? ");
					Object obj = info.invoke(mappedObject, (Object[]) null);
					ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
					paramSet.add(param);
				}
			}
			if (sbClause.length() > 3)
			{
				sbClause.delete(0, 3);
				sbClause.insert(0, " WHERE ");
			}

			String query = sb.toString();
			query = StringUtil.replace(query, "%WHERE%", sbClause.toString(), 1);
			sb = null;
			sbClause = null;

			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createInsertQuery(T mappedObject) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);
			checkMapping(mappedObject, map);
			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO ");
			sb.append(tableName);
			sb.append(" (%COLUMNS%) VALUES (%QUESTIONMARK%) ");

			StringBuffer sbColumns = new StringBuffer();
			StringBuffer sbQuestion = new StringBuffer();

			for (Method info : mappedObject.getClass().getMethods())
			{
				ColumnMapping att = info.getAnnotation(ColumnMapping.class);
				if (att == null) continue;
				if (!info.getName().startsWith("get")) continue;
				if (att.isVirtual()) continue;
				// Property에서 값을 읽어온다.
				sbColumns.append("," + att.columnName());
				if (att.insertQuery().length() > 0)
					sbQuestion.append(",(" + att.insertQuery() + ")");
				else
				{
					sbQuestion.append(",?");
					Object obj = info.invoke(mappedObject, (Object[]) null);
					ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
					paramSet.add(param);
				}
			}
			sbColumns.deleteCharAt(0);
			sbQuestion.deleteCharAt(0);
			String query = sb.toString();
			query = query.replaceAll("%COLUMNS%", sbColumns.toString());
			query = query.replaceAll("%QUESTIONMARK%", sbQuestion.toString());
			sb = null;
			sbColumns = null;
			sbQuestion = null;
			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createUpdateQuery(T mappedObject) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			ArrayList<ParameterSet> tempArr = new ArrayList<ParameterSet>();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);
			checkMapping(mappedObject, map);
			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE ");
			sb.append(tableName);
			sb.append(" SET ");
			sb.append(" %COLUMNS% %WHERE% ");

			StringBuffer sbColumns = new StringBuffer();
			StringBuffer sbClause = new StringBuffer();
			int pkCount = 0;

			for (Method info : mappedObject.getClass().getMethods())
			{
				ColumnMapping att = info.getAnnotation(ColumnMapping.class);
				if (att == null) continue;
				if (!info.getName().startsWith("get")) continue;
				if (att.isVirtual()) continue;
				if (!att.isPk())
				{
					sbColumns.append("," + att.columnName() + " = ?");
					Object obj = info.invoke(mappedObject, (Object[]) null);
					ParameterSet param = new ParameterSet("default", att.type(), obj);
					paramSet.add(param);
				}
				else
				{
					sbClause.append("AND " + att.columnName() + " = ? ");
					pkCount++;
					Object obj = info.invoke(mappedObject, (Object[]) null);
					ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
					tempArr.add(param);
				}
			}

			if (pkCount == 0) throw new InvalidClassException("Primary Key가 없습니다.");
			sbColumns.deleteCharAt(0);
			if (sbClause.length() > 3)
			{
				sbClause.delete(0, 3);
				sbClause.insert(0, " WHERE ");
			}
			String query = sb.toString();
			query = query.replaceAll("%COLUMNS%", sbColumns.toString());
			query = query.replaceAll("%WHERE%", sbClause.toString());
			paramSet.addAll(tempArr);
			tempArr.clear();
			sb = null;
			sbColumns = null;
			sbClause = null;

			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createDeleteQuery(T mappedObject) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);
			checkMapping(mappedObject, map);

			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			sb.append("DELETE FROM ");
			sb.append(tableName);
			sb.append(" %WHERE% ");

			StringBuffer sbClause = new StringBuffer();
			int pkCount = 0;

			for (Method info : mappedObject.getClass().getMethods())
			{
				ColumnMapping att = info.getAnnotation(ColumnMapping.class);
				if (att == null) continue;
				if (!att.isPk()) continue;
				if (!info.getName().startsWith("get")) continue;
				sbClause.append("AND " + att.columnName() + " = ? ");
				pkCount++;
				Object obj = info.invoke(mappedObject, (Object[]) null);
				ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
				paramSet.add(param);
			}
			if (pkCount == 0) throw new InvalidClassException("Primary Key가 없습니다.");
			if (sbClause.length() > 3)
			{
				sbClause.delete(0, 3);
				sbClause.insert(0, " WHERE ");
			}
			String query = sb.toString();
			query = query.replaceAll("%WHERE%", sbClause.toString());
			sb = null;
			sbClause = null;

			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createReadQuery(T mappedObject) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);

			checkMapping(mappedObject, map);

			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT * FROM ");
			sb.append(tableName);
			sb.append(" WHERE %WHERE% ");

			StringBuffer sbClause = new StringBuffer();
			int pkCount = 0;

			for (Method info : mappedObject.getClass().getMethods())
			{
				ColumnMapping att = info.getAnnotation(ColumnMapping.class);
				if (att == null) continue;
				if (!att.isPk()) continue;
				if (att.isVirtual()) continue;
				if (!info.getName().startsWith("get")) continue;
				sbClause.append("AND " + att.columnName() + " = ? ");
				pkCount++;
				Object obj = info.invoke(mappedObject, (Object[]) null);
				ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
				paramSet.add(param);
			}
			if (pkCount == 0) throw new InvalidClassException("Primary Key가 없습니다.");
			if (sbClause.length() > 3) sbClause.delete(0, 3);
			String query = sb.toString();
			query = query.replaceAll("%WHERE%", sbClause.toString());
			sb = null;
			sbClause = null;

			return query;
		}

		/**
		 * 
		 * @param mappedObject
		 * @param columns
		 * @param orderColumn
		 * @param page
		 * @param pageSize
		 * @return
		 * @throws InvalidClassException
		 * @throws IllegalAccessException
		 * @throws IllegalArgumentException
		 * @throws InvocationTargetException
		 */
		public <T extends ObjectMappedEntityBase> String createListQuery(T mappedObject, String[] columns, String[] orderColumn, int page, int pageSize) throws InvalidClassException, IllegalAccessException, IllegalArgumentException, InvocationTargetException
		{
			paramSet.clear();
			TableMapping map = mappedObject.getClass().getAnnotation(TableMapping.class);

			checkMapping(mappedObject, map);

			String tableName = map.tableName();
			StringBuffer sb = new StringBuffer();
			if (page > 0 && pageSize > 0) sb.append("SELECT * FROM (SELECT rownum table_rnum, xx.* FROM (");
			sb.append("SELECT a.* FROM ");
			sb.append(tableName + " a ");
			sb.append(" %WHERE% %ORDER% ");
			if (page > 0 && pageSize > 0) sb.append(" ) xx ) %ROWBOUNDS%");

			StringBuffer sbClause = new StringBuffer();
			StringBuffer sbRowBounds = new StringBuffer();

			if (columns != null && columns.length > 0)
			{
				for (Method info : mappedObject.getClass().getMethods())
				{
					ColumnMapping att = info.getAnnotation(ColumnMapping.class);
					if (att == null) continue;
					if (att.isVirtual()) continue;
					boolean isFind = false;
					for (String column : columns)
					{
						if (att.columnName().equalsIgnoreCase(column))
						{
							isFind = true;
							break;
						}
					}
					if (!isFind) continue;
					if (!info.getName().startsWith("get")) continue;
					sbClause.append("AND " + att.columnName() + " = ? ");
					Object obj = info.invoke(mappedObject, (Object[]) null);
					ParameterSet param = new ParameterSet(dbc.getConnectionName(), att.type(), obj);
					paramSet.add(param);
				}
			}
			StringBuffer sbOrder = new StringBuffer();
			if (orderColumn != null)
			{
				for (String s : orderColumn)
				{
					if (s.startsWith("+"))
						sbOrder.append(", " + s.substring(1) + " ASC");
					else if (s.startsWith("-"))
						sbOrder.append(", " + s.substring(1) + " DESC");
					else
						sbOrder.append(", " + s + " ASC");
				}
				if (sbOrder.length() > 0)
				{
					sbOrder.delete(0, 1);
					sbOrder.insert(0, "ORDER BY ");
				}
			}

			if (page > 0 && pageSize > 0)
			{
				sbRowBounds.append("WHERE table_rnum BETWEEN ? AND ? ");
				ParameterSet paramStart = new ParameterSet("default", Types.INTEGER, (page - 1) * pageSize + 1);
				ParameterSet paramEnd = new ParameterSet(dbc.getConnectionName(), Types.INTEGER, (page) * pageSize);
				paramSet.add(paramStart);
				paramSet.add(paramEnd);
			}
			if (sbClause.length() > 3)
			{
				sbClause.delete(0, 3);
				sbClause.insert(0, " WHERE ");
			}

			String query = sb.toString();
			query = query.replaceAll("%WHERE%", sbClause.toString());
			query = query.replaceAll("%ORDER%", sbOrder.toString());
			query = query.replaceAll("%ROWBOUNDS%", sbRowBounds.toString());
			sb = null;
			sbClause = null;

			return query;
		}
	}

	/**
	 * 기본 생성자.
	 */
	public ObjectMappedEntityBase()
	{
	}

	/**
	 * 클래스의 매핑 정보를 가지고 데이터를 Insert합니다.
	 * 
	 * @return 처리된 행
	 * @throws SMException
	 */
	public int create() throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		String query = null;
		Connection cn = null;
		try
		{
			query = qb.createInsertQuery(this);
			cn = super.getConnection();
			int result = execute(cn, query, qb.getParameterSet());

			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			if (cn != null && !isExternalConnection())
			{
				try
				{
					cn.close();
					cn = null;
				}
				catch (SQLException e)
				{
				}
			}
		}

	}

	/**
	 * 클래스의 매핑 정보를 가지고 데이터를 Update합니다. (isPk = true로 설정된 컬럼을 key로 사용합니다.)
	 * 
	 * @return 처리된 행
	 * @throws SMException
	 */
	public int update() throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		String query = null;
		Connection cn = null;
		try
		{
			query = qb.createUpdateQuery(this);
			cn = super.getConnection();
			int result = execute(cn, query, qb.getParameterSet());

			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
		finally
		{
			if (cn != null && !isExternalConnection())
			{
				try
				{
					cn.close();
					cn = null;
				}
				catch (SQLException e)
				{
				}
			}
		}
	}

	/**
	 * 클래스의 매핑 정보를 가지고 데이터를 Delete합니다. (isPk = true로 설정된 컬럼을 key로 사용합니다.)
	 * 
	 * @return 처리된 행
	 * @throws SMException
	 */
	public int delete() throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		String query = null;
		Connection cn = null;
		try
		{
			query = qb.createDeleteQuery(this);
			cn = super.getConnection();
			int result = execute(cn, query, qb.getParameterSet());

			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			if (cn != null && !isExternalConnection())
			{
				try
				{
					cn.close();
					cn = null;
				}
				catch (SQLException e)
				{
				}
			}
		}
	}

	/**
	 * 클래스의 매핑 정보를 가지고 데이터를 fetch합니다. (isPk = true로 설정된 컬럼을 key로 사용합니다.)
	 * 
	 * @return 읽었는지 여부
	 * @throws SMException
	 */
	public boolean read() throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		String query = null;
		try
		{
			query = qb.createReadQuery(this);
			cn = super.getConnection();
			pstmt = cn.prepareStatement(query);
			int paramIndex = 0;
			for (ParameterSet ps : qb.getParameterSet())
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
			}
			rs = pstmt.executeQuery();

			// 2011-04-06 MSSQL에서는 아래구문 적용시 정방향 전용 결과 집합에서 지원되지 않습니다
			// 라는 오류 발생함
			// 필수 사항이 아닌것으로 보여 주석처리함

			DBConfig dbc = this.getDBConfg();
			if (dbc.getDbms().toUpperCase() != "MSSQL" || dbc.getDbms().toUpperCase() != "MSSQL2000")
			{
				rs.setFetchDirection(ResultSet.FETCH_FORWARD);
			}
			result = bind(rs);

			rs.close();
			rs = null;
			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch (SQLException e)
			{
			}

			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (SQLException e)
			{
			}

			if (!isExternalConnection())
			{
				try
				{
					if (cn != null)
					{
						cn.close();
						cn = null;
					}

				}
				catch (SQLException e)
				{
				}
			}
		}
	}

	private int execute(Connection cn, String query, ArrayList<ParameterSet> paramSet) throws SQLException, Exception
	{
		PreparedStatement pstmt = null;
		try
		{
			pstmt = cn.prepareStatement(query);
			int paramIndex = 0;
			for (ParameterSet ps : paramSet)
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				if (ps.getColumnType() != Types.LONGVARCHAR)
				{
					pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
				}
				else
				{
					DBConfig dbc = this.getDBConfg();
					if (dbc.getDbms().toUpperCase().equals("INFORMIX"))
					{
						pstmt.setBytes(paramIndex, ps.getValue().toString().getBytes("UTF-8"));
					}
					else
					{
						StringReader sr = new StringReader(ps.getValue().toString());
						pstmt.setCharacterStream(paramIndex, sr, ps.getValue().toString().length());
					}
				}
			}
			int result = pstmt.executeUpdate();

			return result;

		}
		finally
		{
			if (pstmt != null)
			{
				try
				{
					pstmt.close();
					pstmt = null;
				}
				catch (Exception e)
				{
				}
			}
			if (cn != null && !isExternalConnection())
			{
				try
				{
					cn.close();
					cn = null;
				}
				catch (Exception e)
				{
				}
			}
		}
	}

	/**
	 * 컬럼을 지정하여 카운트를 센다.
	 * 
	 * @param columnNames
	 * @return
	 * @throws SMException
	 */
	public int getCount(String[] columnNames) throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;
		String query = null;
		try
		{
			query = qb.createCountQuery(this, columnNames);
			cn = super.getConnection();
			pstmt = cn.prepareStatement(query);
			int paramIndex = 0;
			for (ParameterSet ps : qb.getParameterSet())
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
			}
			rs = pstmt.executeQuery();
			if (rs.next()) result = rs.getInt("rowcnt"); // 첫번째 rowcount는 mssql
															// 예약어
			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (cn != null && !isExternalConnection())
				{
					cn.close();
					cn = null;
				}
			}
			catch (SQLException e)
			{
			}
		}
	}

	/**
	 * 클래스의 매핑 정보를 가지고 지정된 컬럼(columnName)으로 제한하고, orderHintColumns로 Ordering한
	 * 데이터를 fetch합니다.<br>
	 * paging할 때 씁니다.
	 * 
	 * @param columnNames
	 *            제한할 컬럼명 리스트
	 * @param orderHintColumns
	 *            정렬할 컬럼명 (컬럼명 앞에 + 또는 -기호를 넣어서 정순인지 역순인지 구별한다)
	 * @param page
	 *            읽어올 페이지
	 * @param pageSize
	 *            읽어올 사이즈
	 * @return ListHelper Instance
	 * @throws SMException
	 * @see ListHelper
	 */
	public <T extends ObjectMappedEntityBase> ListHelper<T> list(String[] columnNames, String[] orderHintColumns, int page, int pageSize) throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<T> arr = new ArrayList<T>();
		ListHelper<T> helper = new ListHelper<T>(arr, 0, page, pageSize, 10);
		String query = null;
		try
		{
			query = qb.createListQuery(this, columnNames, orderHintColumns, page, pageSize);
			cn = super.getConnection();
			pstmt = cn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			int paramIndex = 0;
			for (ParameterSet ps : qb.getParameterSet())
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
			}
			rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			ArrayList<String> columnList = new ArrayList<String>();

			for (int i = 1; i <= rsmd.getColumnCount(); i++)
			{
				columnList.add(rsmd.getColumnName(i).toLowerCase());
			}

			while (rs.next())
			{
				ObjectMappedEntityBase obj = this.getClass().newInstance();
				Method[] method = this.getClass().getMethods();

				for (int index = 1; index <= rsmd.getColumnCount(); index++)
				{
					for (Method m : method)
					{
						if (!m.isAnnotationPresent(ColumnMapping.class)) continue;
						if (!m.getName().startsWith("set")) continue;

						ColumnMapping mapping = m.getAnnotation(ColumnMapping.class);

						if (!mapping.columnName().equalsIgnoreCase(rsmd.getColumnName(index))) continue;

						Object o = rs.getObject(index);

						if (o == null)
						{
							if (m.getParameterTypes()[0].isPrimitive())
							{
								m.invoke(obj, ObjectMappedEntityBase.primitiveDefaults.get(m.getParameterTypes()[0]));
							}
							continue;
						}

						switch (mapping.type())
						{
							case Types.DATE:
								Date date = new Date(rs.getDate(index).getTime());
								m.invoke(obj, date);
								break;
							case Types.FLOAT:
								m.invoke(obj, rs.getFloat(index));
								break;
							case Types.INTEGER:
								m.invoke(obj, rs.getInt(index));
								break;
							default:
								m.invoke(obj, o);
								break;
						}
					}
				}
				arr.add((T) obj);
			}
			cn.close();
			cn = null;
			// 최종적으로 count를 돌려준다.
			helper.setTotalSize(this.getCount(columnNames));

			return helper;
		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (cn != null && !isExternalConnection())
				{
					cn.close();
					cn = null;
				}

			}
			catch (SQLException e)
			{
			}
		}
	}

	/**
	 * 클래스의 매핑 정보를 가지고 지정된 컬럼(columnName)으로 제한하고, orderHintColumns로 Ordering한
	 * 데이터를 fetch합니다.<br>
	 * 
	 * @param columnNames
	 *            제한할 컬럼명 리스트
	 * @param orderHintColumns
	 *            정렬할 컬럼명 (컬럼명 앞에 + 또는 -기호를 넣어서 정순인지 역순인지 구별한다)
	 * @return 객체의 Instance들이 담긴 ArrayList
	 * @throws SMException
	 */
	public <T extends ObjectMappedEntityBase> ArrayList<T> list(String[] columnNames, String[] orderHintColumns) throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<T> arr = new ArrayList<T>();
		String query = null;
		try
		{
			query = qb.createListQuery(this, columnNames, orderHintColumns, 0, 0);
			cn = super.getConnection();
			pstmt = cn.prepareStatement(query);
			int paramIndex = 0;
			for (ParameterSet ps : qb.getParameterSet())
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
			}

			rs = pstmt.executeQuery();

			ResultSetMetaData rsmd = rs.getMetaData();
			ArrayList<String> columnList = new ArrayList<String>();

			for (int i = 1; i <= rsmd.getColumnCount(); i++)
			{
				columnList.add(rsmd.getColumnName(i).toLowerCase());
			}

			while (rs.next())
			{
				ObjectMappedEntityBase obj = this.getClass().newInstance();
				Method[] method = this.getClass().getMethods();

				for (int index = 1; index <= rsmd.getColumnCount(); index++)
				{
					for (Method m : method)
					{
						if (!m.isAnnotationPresent(ColumnMapping.class)) continue;
						if (!m.getName().startsWith("set")) continue;

						ColumnMapping mapping = m.getAnnotation(ColumnMapping.class);

						if (!mapping.columnName().equalsIgnoreCase(rsmd.getColumnName(index))) continue;

						Object o = rs.getObject(index);

						if (o == null)
						{
							if (m.getParameterTypes()[0].isPrimitive())
							{
								m.invoke(obj, ObjectMappedEntityBase.primitiveDefaults.get(m.getParameterTypes()[0]));
							}
							continue;
						}

						switch (mapping.type())
						{
							case Types.DATE:
								Date date = new Date(rs.getDate(index).getTime());
								m.invoke(obj, date);
								break;
							case Types.FLOAT:
								m.invoke(obj, rs.getFloat(index));
								break;
							case Types.INTEGER:
								m.invoke(obj, rs.getInt(index));
								break;
							case Types.LONGVARCHAR:
							{
								DBConfig dbc = this.getDBConfg(); // default로만
								if (dbc.getDbms().toUpperCase().equals("INFORMIX"))
									m.invoke(obj, rs.getString(index));
								else
									m.invoke(obj, o);
							}
								break;
							default:
								m.invoke(obj, o);
								break;
						}
					}
				}
				arr.add((T) obj);
			}
			return arr;
		}
		catch (SQLException ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException("오류 : " + ex.getMessage(), ex);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (cn != null && !isExternalConnection())
				{
					cn.close();
					cn = null;
				}

			}
			catch (SQLException e)
			{
			}
		}
	}

	/**
	 * 클래스의 매핑 정보를 가지고 지정된 컬럼(columnName)으로 제한한 데이터를 fetch합니다.<br>
	 * 
	 * @param columnNames
	 *            제한할 컬럼명 리스트
	 * @return 객체의 Instance들이 담긴 ArrayList
	 * @throws SMException
	 */
	public <T extends ObjectMappedEntityBase> ArrayList<T> list(String[] columnNames) throws SMException
	{
		return list(columnNames, null);
	}

	/**
	 * ResultSet으로 객체에 Binding할때 씁니다.
	 * 
	 * @param rs
	 *            활성화된 ResultSet
	 * @return rs.next()가 수행되면 true, 그렇지 않으면 false를 return합니다.
	 * @throws SMException
	 */
	public boolean bind(ResultSet rs) throws SMException
	{
		try
		{
			if (rs == null) throw new SQLException("ResultSet이 null입니다.");
			if (!rs.next()) return false;

			ResultSetMetaData rsmd = rs.getMetaData();
			ArrayList<String> columnList = new ArrayList<String>();

			for (int i = 1; i <= rsmd.getColumnCount(); i++)
			{
				columnList.add(rsmd.getColumnName(i).toLowerCase());
			}

			Method[] method = this.getClass().getMethods();
			// Step 1. 컬럼에서 ColumnMapping이 있는 것들만 추출한다.

			List<Method> list = new ArrayList<Method>();
			List<Method> longTypeList = new ArrayList<Method>();
			for (Method m : method)
			{
				if (!m.isAnnotationPresent(ColumnMapping.class)) continue;
				if (!m.getName().startsWith("set")) continue;

				ColumnMapping mapping = m.getAnnotation(ColumnMapping.class);

				if (mapping.type() == Types.LONGVARCHAR)
					longTypeList.add(m);
				else
					list.add(m);
			}

			// Step 2. text형 데이터부터 fetch한다. (oracle의 경우 stream이 닫혔다는 오류가 발생하기
			// 때문에..)
			for (int index = 1; index <= rsmd.getColumnCount(); index++)
			{
				for (Method m : longTypeList)
				{
					ColumnMapping mapping = m.getAnnotation(ColumnMapping.class);

					if (!mapping.columnName().equalsIgnoreCase(rsmd.getColumnName(index))) continue;

					Reader reader = null;
					StringBuffer buff = new StringBuffer();

					try
					{
						DBConfig dbc = this.getDBConfg();
						if (dbc.getDbms().toUpperCase().equals("INFORMIX"))
						{
							byte[] f = rs.getBytes(index);
							buff.append(new String(f, "UTF-8"));
						}
						else
						{
							reader = rs.getCharacterStream(index);
							if (reader != null)
							{
								char[] ch = new char[512];
								int len = -1;
								while ((len = reader.read(ch)) != -1)
								{
									buff.append(ch, 0, len);
								}
							}
						}
						m.invoke(this, buff.toString());
					}
					catch (Exception e)
					{
						e.getStackTrace();
					}
					finally
					{
						buff = null;
						reader = null;
					}

					continue;
				}
			}
			// Step 3. 다른 데이터를 fetch한다.
			for (int index = 1; index <= rsmd.getColumnCount(); index++)
			{
				for (Method m : list)
				{
					ColumnMapping mapping = m.getAnnotation(ColumnMapping.class);

					if (!mapping.columnName().equalsIgnoreCase(rsmd.getColumnName(index))) continue;

					Object o = rs.getObject(index);

					if (o == null)
					{
						if (m.getParameterTypes()[0].isPrimitive())
						{
							m.invoke(this, ObjectMappedEntityBase.primitiveDefaults.get(m.getParameterTypes()[0]));
						}
						continue;
					}

					switch (mapping.type())
					{
						case Types.DATE:
							Date date = new Date(rs.getDate(index).getTime());
							m.invoke(this, date);
							break;
						case Types.FLOAT:
							m.invoke(this, rs.getFloat(index));
							break;
						case Types.INTEGER:
							m.invoke(this, rs.getInt(index));
							break;
						case Types.BIGINT:
							m.invoke(this, rs.getLong(index));
							break;
						case Types.LONGVARCHAR:
							break;
						default:
							m.invoke(this, o);
							break;
					}
				}
			}
			return true;
		}
		catch (SQLException ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
	}

	/**
	 * isPk로 설정된 컬럼값으로 제한해서 해당 row가 있는지 여부를 리턴합니다.
	 * 
	 * @return
	 * @throws SMException
	 */
	public boolean isExist() throws SMException
	{
		QueryBuilder qb = new QueryBuilder(this.getDBConfg());
		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean result = false;
		String query = null;
		try
		{
			query = qb.createExistQuery(this);
			cn = super.getConnection();
			pstmt = cn.prepareStatement(query);
			int paramIndex = 0;
			for (ParameterSet ps : qb.getParameterSet())
			{
				paramIndex++;
				if (ps.getValue() == null)
				{
					pstmt.setNull(paramIndex, ps.getColumnType());
					continue;
				}
				pstmt.setObject(paramIndex, ps.getValue(), ps.getColumnType());
			}
			rs = pstmt.executeQuery();

			result = rs.next();

			rs.close();
			rs = null;
			return result;

		}
		catch (SQLException ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
		catch (Exception ex)
		{
			throw new SMException(ex.getMessage(), ex);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch (SQLException e)
			{
			}
			try
			{
				if (cn != null && !isExternalConnection())
				{
					cn.close();
					cn = null;
				}

			}
			catch (SQLException e)
			{
			}
		}
	}

	/**
	 * 객체를 가지고 XML데이터를 생성한다. XML루트 Element명은 TableMapping의 Table명, 가상컬럼은 포함하지
	 * 않는다.
	 * 
	 * @return
	 * @throws SMException
	 */
	protected String generateXML() throws SMException
	{
		return generateXML(false);
	}

	/**
	 * 객체를 가지고 XML데이터를 생성한다. XML루트 Element명은 TableMapping의 Table명
	 * 
	 * @param includeVirtual
	 *            가상 컬럼도 포함할지 여부
	 * @return
	 * @throws SMException
	 */
	protected String generateXML(boolean includeVirtual) throws SMException
	{
		return generateXML(includeVirtual, null);
	}

	/**
	 * 객체를 가지고 XML데이터를 생성한다.
	 * 
	 * @param includeVirtual
	 *            가상 컬럼도 포함할지 여부
	 * @param rootName
	 *            XML루트 Element명 (생략시 TableMapping의 Table명)
	 * @return
	 * @throws SMException
	 */
	protected String generateXML(boolean includeVirtual, String rootName) throws SMException
	{
		Document doc = new Document();

		TableMapping tm = this.getClass().getAnnotation(TableMapping.class);

		if (rootName == null || rootName.length() == 0)
		{
			if (tm == null)
				rootName = "DATA";
			else
				rootName = tm.tableName();
		}

		Element root = new Element(rootName.toUpperCase());
		doc.addContent(root);

		Method[] methods = this.getClass().getMethods();
		for (Method m : methods)
		{
			if (!m.getName().startsWith("get")) continue;
			ColumnMapping map = m.getAnnotation(ColumnMapping.class);
			if (map == null) continue;
			if (map.isVirtual() && !includeVirtual) continue;
			try
			{
				Object nullVar = null;
				XmlDocument.appendChild(root, map.columnName().toUpperCase(), m.invoke(this, nullVar));
			}
			catch (Exception e)
			{
				throw new SMException("XML 데이터 생성중 오류 발생 : " + e.getMessage(), e);
			}
		}

		XMLOutputter output = new XMLOutputter();
		StringWriter writer = new StringWriter();

		try
		{
			output.output(doc, writer);
		}
		catch (Exception e)
		{
			throw new SMException("XML 데이터 생성중 오류 발생 : " + e.getMessage(), e);
		}
		return writer.toString();
	}

	/**
	 * 
	 * @param table
	 * @param column
	 * @param where
	 * @return
	 * @throws SMException
	 */
	public int getMaxID(String table, String column, String where) throws SMException
	{
		String sql;
		Connection cn = null;
		try
		{
			sql = ConnectionFactory.newInstance().createMaxQuery(table, column, where);
			DBHelper helper = new DBHelper(sql);
			if (isExternalConnection())
			{
				cn = super.getConnection();
				helper.setConnection(cn);
			}
			int result = helper.executeInt();
			return result;
		}
		catch (Exception e)
		{
			e.printStackTrace();
			return -1;
		}
		finally
		{
			if (cn != null && !isExternalConnection())
			{
				try
				{
					cn.close();
					cn = null;
				}
				catch (SQLException e1)
				{
				}
			}
		}
	}

	@Override
	public Connection getConnection() throws SQLException
	{
		if (isExternalConnection()) return super.getConnection();
		return null;
	}
}
